package com.deng.common.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.dao.IClassType;
import com.deng.common.vo.ClassTypeVo;
import com.deng.util.DateTools;

public class ClassTypeImpl extends SQLHelper implements IClassType {

	public List<ClassTypeVo> getAllClass() {
		try{
			Connection conn = getConnect();
			String sql = "select * from DICT_CLASS_TYPE order by name";
			ResultSet rs = queryData(conn,sql);
			List<ClassTypeVo> l = new ArrayList<ClassTypeVo>();
			while(rs.next()){
				ClassTypeVo vo = new ClassTypeVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				if(rs.getBigDecimal("point") != null){
					vo.point = rs.getBigDecimal("point").toString();
				}
				l.add(vo);
			}			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public ClassTypeVo getClassById(int id) {
		try{
			Connection conn = getConnect();
			String sql = "select * from DICT_CLASS_TYPE where id = "+id;
			ResultSet rs = queryData(conn,sql);
			List<ClassTypeVo> l = new ArrayList<ClassTypeVo>();
			if(rs.next()){
				ClassTypeVo vo = new ClassTypeVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				if(rs.getBigDecimal("point") != null){
					vo.point = rs.getBigDecimal("point").toString();
				}
				freeConnection(conn);
				return vo;
			}			
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public int deleteEmpClassData(ClassTypeVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "delete t_stat_employee_vs_class_month" +
					"	where class_id = " +vo.id+
					"	and employee_id = " +vo.employee_id+
					"	and year(stat_time) = " +vo.stat_time_y+
					"	and month(stat_time) = "+vo.stat_time_m;
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int insertEmpClassData(ClassTypeVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "insert into t_stat_employee_vs_class_month" +
					"	(class_id,employee_id,stat_time,count,dispense_organization_id,point)" +
					"	values("+vo.id+","+vo.employee_id+",'"+vo.stat_time_y+"-"+vo.stat_time_m+"-1'," +
					"	"+vo.count+","+vo.dispense_id+","+vo.point+")";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int updateEmpClassData(ClassTypeVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_stat_employee_vs_class_month" +
					"	set count = "+vo.count+
					"	where class_id = " +vo.id+
					"	and employee_id = " +vo.employee_id+
					"	and year(stat_time) = " +vo.stat_time_y+
					"	and month(stat_time) = "+vo.stat_time_m;
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public List<ClassTypeVo> searchEmpClass(int dispense_id, int employee_id,
			String y, String m) {
		try{
			Connection conn = getConnect();
			String sql = "select "+dispense_id+" as dispense_id,DICT_CLASS_TYPE.id,DICT_CLASS_TYPE.name," +
					"	DICT_CLASS_TYPE.point,"+employee_id+" as employee_id," +
					"	'"+y+"-"+m+"-1' as stat_time," +
					"	t_stat_employee_vs_class_month.count," +
					"	t_stat_employee_vs_class_month.valid  " +
					"	from t_stat_employee_vs_class_month " +
					"	right join DICT_CLASS_TYPE " +
					"	on t_stat_employee_vs_class_month.class_id = DICT_CLASS_TYPE.id " +
					"	and t_stat_employee_vs_class_month.dispense_organization_id =  "+dispense_id +
					"	and year(t_stat_employee_vs_class_month.stat_time) = " +y+
					"	and month(t_stat_employee_vs_class_month.stat_time) = " +m+
					"	and t_stat_employee_vs_class_month.employee_id =  " +employee_id+
					"	order by name";
			ResultSet rs = queryData(conn,sql);
			List<ClassTypeVo> l = new ArrayList<ClassTypeVo>();
			while(rs.next()){
				ClassTypeVo vo = new ClassTypeVo();
				vo.id = rs.getInt("id");
				vo.name = rs.getString("name");
				vo.count = rs.getBigDecimal("count")!=null?rs.getBigDecimal("count").toString():null;
				vo.dispense_id = rs.getInt("dispense_id");
				vo.employee_id = rs.getInt("employee_id");
				vo.point =rs.getBigDecimal("point")!=null?rs.getBigDecimal("point").toString():null;
				vo.stat_time_y = y;
				vo.stat_time_m = m;
				vo.valid = rs.getInt("valid");
				l.add(vo);
			}			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public int validEmpClass(List<ClassTypeVo> lst, String user_code) {
		try{
			if(lst == null || lst.size() == 0){
				return 0;
			}
			Connection conn = getConnect();
			for(int i =0;i<lst.size();i++){
				ClassTypeVo vo = lst.get(i);
				String sql = "update t_stat_employee_vs_class_month" +
							"	set valid = 1, " +
							"	valid_user_code = '"+user_code+"',"+
							"	valid_time = '"+DateTools.dtsWithTime(new Date())+"' " +
							"	where class_id = "+vo.id+
							"	and dispense_organization_id = "+ vo.dispense_id +
							"	and employee_id = "+vo.employee_id+
							"	and year(stat_time) = "+vo.stat_time_y+
							"	and month(stat_time) = "+vo.stat_time_m;
				updateData(conn, sql);
			}
			freeConnection(conn);
			return 1;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

}
